﻿package com.mms.dao;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.omg.CORBA.PRIVATE_MEMBER;

import com.mms.entity.Department;
import com.mms.entity.InvitationMessage;
import com.mms.entity.Meeting;
import com.mms.entity.MeetingRoom;
import com.mms.util.HibernateUtil;
import com.sun.corba.se.spi.orbutil.fsm.Guard.Result;

public class MeetingRoomManagementDao {

	public boolean AddMeetingRoom(MeetingRoom meetingRoom){
		Session session = HibernateUtil.getSession();
		try {
			Transaction trans = session.beginTransaction();
			session.save(meetingRoom);
			trans.commit();
			return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
       }finally{
    	   session.close();
       }
	}
	
	/*
	 * �޸Ļ�����
	 */
	public boolean ModifyMeetingRoom(MeetingRoom meetingRoom){
			Session session = HibernateUtil.getSession();
		try{
			Transaction transaction = session.beginTransaction();
			session.update(meetingRoom);
			transaction.commit();
			return true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}finally{
			session.close();
		}
	}
	
	/*
	 * ɾ�������
	 */
	public boolean DeleteMeetingRoom(MeetingRoom meetingRoom){
			Session session = HibernateUtil.getSession();
		try{
			Transaction transaction = session.beginTransaction();
			session.delete(meetingRoom);
			transaction.commit();
			return true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}finally{
			session.close();
		}
	}
	
	public List<MeetingRoom> getMeetingRooms(int spage) {
		List<Object[]> objs;
        List<MeetingRoom> meetingRooms =new ArrayList<MeetingRoom>();
        Session session = HibernateUtil.getSession();
        try {
            String sql="select * from meeting_room limit ?,6";
            Query query = session.createSQLQuery(sql);
            Transaction transaction = session.beginTransaction();
            query.setInteger(0,spage*6);
            objs = query.list();
            for (Object[] objects : objs) {
                MeetingRoom meetingRoom = new MeetingRoom();
                meetingRoom.setMrId(Integer.parseInt((objects[0].toString())));
                meetingRoom.setCapacity(Short.parseShort((objects[1].toString())));
                meetingRoom.setState(Integer.parseInt((objects[2].toString())));
                meetingRoom.setMrName((objects[3].toString()));
                meetingRooms.add(meetingRoom);
            }
            transaction.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	session.close();
        }
        return meetingRooms;
    }
	
	public List<MeetingRoom> getMeetingRooms() {
		List<Object[]> objs;
        List<MeetingRoom> meetingRooms =new ArrayList<MeetingRoom>();
        Session session = HibernateUtil.getSession();
        try {
            String sql="select * from meeting_room";
            Transaction transaction = session.beginTransaction();
            Query query = session.createSQLQuery(sql);
            objs = query.list();
            for (Object[] objects : objs) {
                MeetingRoom meetingRoom = new MeetingRoom();
                meetingRoom.setMrId(Integer.parseInt((objects[0].toString())));
                meetingRoom.setCapacity(Short.parseShort((objects[1].toString())));
                meetingRoom.setState(Integer.parseInt((objects[2].toString())));
                meetingRoom.setMrName((objects[3].toString()));
                meetingRooms.add(meetingRoom);
            }
            transaction.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	session.close();
        }
        return meetingRooms;
    }
	public List<MeetingRoom> getMeetingRoomsByTimeAndCapacity(String startTime,String endTime,int capacity) {
		List<Object[]> objs;
        List<MeetingRoom> meetingRooms =new ArrayList<MeetingRoom>();
        Session session = HibernateUtil.getSession();
        try {
            String sql="select * from meeting_room where capacity >= ? and state = ? and mr_name not in ( select mr_name from meeting natural join meeting_room where (start_time < ? and end_time > ?) or  (start_time < ? and end_time > ?) )";
            Transaction transaction = session.beginTransaction();
            Query query = session.createSQLQuery(sql);
            query.setParameter(0,capacity);
            query.setParameter(1,0);
            query.setParameter(2,startTime);
            query.setParameter(3,startTime);
            query.setParameter(4,endTime);
            query.setParameter(5,endTime);
            objs = query.list();
            for (Object[] objects : objs) {
                MeetingRoom meetingRoom =new MeetingRoom();
                meetingRoom.setMrId(Integer.parseInt((objects[0].toString())));
                meetingRoom.setCapacity(Short.parseShort((objects[1].toString())));
                meetingRoom.setState(Integer.parseInt((objects[2].toString())));
                meetingRoom.setMrName((objects[3].toString()));
                meetingRooms.add(meetingRoom);
            }
           transaction.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	session.close();
        }
        return meetingRooms;
    }


 public int getcount(){
	  Session session = HibernateUtil.getSession();
	  List<BigInteger> num;
	  int nums = 0;
	 try{
		 String sql = "select count(mr_id) from meeting_room";
		 Transaction transaction = session.beginTransaction();
		 Query query = session.createSQLQuery(sql);
		 num = query.list();
		 nums = Integer.parseInt(num.get(0).toString());
		 transaction.commit();
	 }catch(Exception e){
		 e.printStackTrace();
	 }finally{
		 session.close();
	 }
	 return nums;
 }
 
 public List<MeetingRoom> getSrearchMeetingRooms(String mes) {
	 List<Object[]> objs;
     List<MeetingRoom> meetingRooms =new ArrayList<MeetingRoom>();
     Session session = HibernateUtil.getSession();
     try {
         String sql="select * from meeting_room where mr_name like '%"+mes+"%'";
         Transaction transaction = session.beginTransaction();
         Query query = session.createSQLQuery(sql);
         objs = query.list();
         for (Object[] objects : objs) {
        	 MeetingRoom meetingRoom =new MeetingRoom();
             meetingRoom.setMrId(Integer.parseInt((objects[0].toString())));
             meetingRoom.setCapacity(Short.parseShort((objects[1].toString())));
             meetingRoom.setState(Integer.parseInt((objects[2].toString())));
             meetingRoom.setMrName((objects[3].toString()));
             meetingRooms.add(meetingRoom);
         }
        transaction.commit();
     } catch (Exception e) {
         e.printStackTrace();
     }finally{
     	session.close();
     }
     return meetingRooms;
 	}
}
